const { Sequelize, DataTypes } = require('sequelize');
const { dbPort, host, db, user, password } = require('../config.json');

const sequelize = new Sequelize(db, user, password, {
    host,
    port: dbPort,
    dialect: 'mysql'
});

/**
 * 定义表
 * @param {*} name 
 * @param {*} attrs 
 * @param {*} options 
 * @param {*} type 
 * @returns 
 */
const define = (name, attrs, options, type = DataTypes.INTEGER.UNSIGNED) => {
    return sequelize.define(name, {
        id: {
            primaryKey: true,
            autoIncrement: true,
            type
        },
        ...attrs
    }, {
        tableName: name,
        timestamps: false,
        ...options
    })
}

const dictionary = define('dictionary', {
    name: {
        type: DataTypes.STRING(20),
    },
    type: {
        type: DataTypes.TINYINT.UNSIGNED
    }
})


const province = define('province', {
    province_code: {
        type: DataTypes.STRING(6),
    },
    province: {
        type: DataTypes.STRING(40)
    }
});

const city = define('city', {
    city_code: {
        type: DataTypes.STRING(6),
    },
    city: {
        type: DataTypes.STRING(20),
    },
    p_code: {
        type: DataTypes.STRING(6),
        // references: {
        //     model: province,
        //     key: 'province_code'
        // }
    }
});
// city.belongsTo(province, { foreignKey: 'p_code' });

const area = define('area', {
    area_code: {
        type: DataTypes.STRING(6)
    },
    area: {
        type: DataTypes.STRING(20)
    },
    p_code: {
        type: DataTypes.STRING(6),
        // references: {
        //     model: city,
        //     key: 'city_code'
        // }
    }
});
// area.belongsTo(city, { foreignKey: 'p_code' });

const roles = define('roles', {
    name: {
        type: DataTypes.STRING(20)
    },
    description: {
        type: DataTypes.STRING(100)
    }
}, null, DataTypes.SMALLINT.UNSIGNED);

const users = define('users', {
    role_id: {
        type: DataTypes.SMALLINT.UNSIGNED,
        references: {
            model: roles,
            key: 'id'
        }
    },
    role_name: {
        type: DataTypes.STRING(20),
    },
    username: {
        type: DataTypes.STRING(20)
    },
    password: {
        type: DataTypes.CHAR(32)
    },
    email: {
        type: DataTypes.STRING(20),
        allowNull: true
    },
    phone: {
        type: DataTypes.CHAR(11)
    },
    status: {
        type: DataTypes.BOOLEAN,
        allowNull: true,
        defaultValue: true
    },
    creation_time: {
        type: DataTypes.DATE,
        allowNull: true,
        defaultValue: DataTypes.NOW
    }
});
roles.hasOne(users, { foreignKey: 'role_id' });

const user_address = define('user_address', {
    zip_code: {
        type: DataTypes.CHAR(6)
    },
    phone: {
        type: DataTypes.CHAR(11)
    },
    province: {
        type: DataTypes.STRING(6),
        // references: {
        //     model: province,
        //     key: 'province_code'
        // }
    },
    province_name: {
        type: DataTypes.STRING(20)
    },
    city: {
        type: DataTypes.STRING(6),
        // references: {
        //     model: city,
        //     key: 'city_code'
        // }
    },
    city_name: {
        type: DataTypes.STRING(20)
    },
    district: {
        type: DataTypes.STRING(6),
        // references: {
        //     model: area,
        //     key: 'area_code'
        // }
    },
    district_name: {
        type: DataTypes.STRING(20)
    },
    address: {
        type: DataTypes.STRING(100)
    },
    default: {
        type: DataTypes.BOOLEAN
    },
    user_id: {
        type: DataTypes.INTEGER.UNSIGNED,
        references: {
            model: users,
            key: 'id'
        }
    }
});
// users.hasMany(user_address, { foreignKey: 'user_id' });
user_address.belongsTo(users, { foreignKey: 'user_id' });
// user_address.belongsTo(province, { foreignKey: 'province', as: 'p' });
// user_address.belongsTo(city, { foreignKey: 'city', as: 'c' });
// user_address.belongsTo(area, { foreignKey: 'district' });

const menu_list = define('menu_list', {
    id: {
        type: DataTypes.SMALLINT.UNSIGNED,
        primaryKey: true
    },
    name: {
        type: DataTypes.STRING(20),
    },
    code: {
        type: DataTypes.STRING(20),
    },
    showParent: {
        type: DataTypes.BOOLEAN,
        allowNull: true,
        defaultValue: false
    },
    type: {
        type: DataTypes.INTEGER.UNSIGNED,
        references: {
            model: dictionary,
            key: 'id'
        }
    },
    sort: {
        type: DataTypes.INTEGER.UNSIGNED
    },
    pid: {
        type: DataTypes.INTEGER.UNSIGNED
    }
});
menu_list.belongsTo(dictionary, { foreignKey: 'type' });

const role_menu = define('role_menu', {
    role_id: {
        type: DataTypes.SMALLINT.UNSIGNED,
        references: {
            model: roles,
            key: 'id'
        }
    },
    menu_id: {
        type: DataTypes.SMALLINT.UNSIGNED,
        references: {
            model: menu_list,
            key: 'id'
        }
    }
});
role_menu.belongsTo(roles, { foreignKey: 'role_id' });
role_menu.belongsTo(menu_list, { foreignKey: 'menu_id' });

const classify = define('classify', {
    name: {
        type: DataTypes.STRING(20),
    },
    sort: {
        type: DataTypes.INTEGER.UNSIGNED
    },
    pid: {
        type: DataTypes.INTEGER.UNSIGNED
    }
});

const attrs = define('attrs', {
    class_id: {
        type: DataTypes.INTEGER.UNSIGNED
    },
    name: {
        type: DataTypes.STRING(20),
    }
});
attrs.belongsTo(classify, { foreignKey: 'class_id' });

const product_list = define('product_list', {
    name: {
        type: DataTypes.STRING(100),
        allowNull: false,
    },
    oldPrice: {
        type: DataTypes.DOUBLE(10, 2).UNSIGNED,
        allowNull: false
    },
    price: {
        type: DataTypes.DOUBLE(10, 2).UNSIGNED,
        allowNull: false
    },
    sku: {
        type: DataTypes.SMALLINT.UNSIGNED,
        allowNull: false
    },
    quantity: {
        type: DataTypes.SMALLINT.UNSIGNED,
        allowNull: false
    },
    class_id: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        references: {
            model: classify,
            key: 'id'
        }
    },
    weight: {
        type: DataTypes.FLOAT(8, 2).UNSIGNED,
        allowNull: false
    },
    status: {
        type: DataTypes.BOOLEAN,
        allowNull: true,
        defaultValue: true
    },
    creation_time: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW
    }
}, null, DataTypes.BIGINT.UNSIGNED);
product_list.belongsTo(classify, { foreignKey: 'class_id' });

const product_attrs = define('product_attrs', {
    product_id: {
        type: DataTypes.BIGINT.UNSIGNED,
        allowNull: false,
        references: {
            model: product_list,
            key: 'id'
        }
    },
    attrs_id: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        references: {
            model: attrs,
            key: 'id'
        }
    },
    choice: {
        type: DataTypes.STRING(20),
        allowNull: false
    }
}, null, DataTypes.BIGINT.UNSIGNED);
product_attrs.belongsTo(product_list, { foreignKey: 'product_id' });
product_attrs.belongsTo(attrs, { foreignKey: 'attrs_id' });

const image_list = define('image_list', {
    name: {
        type: DataTypes.STRING(41),
        allowNull: false
    },
    user_id: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        references: {
            model: users,
            key: 'id'
        }
    },
    create_time: {
        type: DataTypes.DATE,
        allowNull: true,
        defaultValue: DataTypes.NOW
    }
}, null, DataTypes.BIGINT.UNSIGNED);
image_list.belongsTo(users, { foreignKey: "user_id" });

define('product_images', {
    product_id: {
        type: DataTypes.BIGINT.UNSIGNED,
        allowNull: false,
        references: {
            model: product_list,
            key: 'id'
        }
    },
    product_attrs: {
        type: DataTypes.STRING(100),
        allowNull: false
    },
    images: {
        type: DataTypes.STRING(500),
        allowNull: false
    }
}, null, DataTypes.BIGINT.UNSIGNED);

const order_list = define('order_ist', {
    logistics_id: {
        type: DataTypes.STRING(18),
    },
    user_address: {
        type: DataTypes.STRING(200),
        allowNull: false
    },
    price: {
        type: DataTypes.DOUBLE(10, 2),
        allowNull: false
    },
    status: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        references: {
            model: dictionary,
            key: 'id'
        }
    },
    creation_time: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW
    },
    payment_time: {
        type: DataTypes.DATE
    }
}, null, DataTypes.BIGINT.UNSIGNED);

const order_with_product = define('order_with_product', {
    order_id: {
        type: DataTypes.BIGINT.UNSIGNED,
        allowNull: false
    },
    product_id: {
        type: DataTypes.BIGINT.UNSIGNED,
        allowNull: false
    }
}, null, DataTypes.BIGINT.UNSIGNED);
product_list.belongsToMany(order_list, { through: 'order_with_product', foreignKey: 'product_id' });

define('logs', {
    message: {
        type: DataTypes.STRING(1000),
        allowNull: false
    },
    date: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW
    }
});

module.exports = {
    sequelize
}